package com.studentmanagement.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.studentmanagement.model.Student;
import com.studentmanagement.util.PageBean;

public class StudentDAO extends BaseDAO<Student> {
    
    // 添加学生
    public int addStudent(Student student) throws SQLException {
        String sql = "INSERT INTO students (student_id, name, gender, birth_date, address, phone, email, enrollment_date, major, status) " +
                     "VALUES (student_seq.NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        return update(sql, student.getName(), student.getGender(), student.getBirthDate(), 
                     student.getAddress(), student.getPhone(), student.getEmail(), 
                     student.getEnrollmentDate(), student.getMajor(), student.getStatus());
    }
    
    // 更新学生信息
    public int updateStudent(Student student) throws SQLException {
        String sql = "UPDATE students SET name = ?, gender = ?, birth_date = ?, address = ?, " +
                     "phone = ?, email = ?, enrollment_date = ?, major = ?, status = ? " +
                     "WHERE student_id = ?";
        
        return update(sql, student.getName(), student.getGender(), student.getBirthDate(), 
                     student.getAddress(), student.getPhone(), student.getEmail(), 
                     student.getEnrollmentDate(), student.getMajor(), student.getStatus(), 
                     student.getStudentId());
    }
    
    // 删除学生
    public int deleteStudent(int studentId) throws SQLException {
        String sql = "DELETE FROM students WHERE student_id = ?";
        return update(sql, studentId);
    }
    
    // 根据ID查询学生
    public Student getStudentById(int studentId) throws SQLException {
        String sql = "SELECT * FROM students WHERE student_id = ?";
        
        return queryForObject(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs) throws SQLException {
                Student student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setBirthDate(rs.getDate("birth_date"));
                student.setAddress(rs.getString("address"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setEnrollmentDate(rs.getDate("enrollment_date"));
                student.setMajor(rs.getString("major"));
                student.setStatus(rs.getString("status"));
                return student;
            }
        }, studentId);
    }
    
    // 分页查询学生列表
    public List<Student> getStudentsByPage(PageBean<Student> pageBean) throws SQLException {
        // 构建三层子查询SQL
        String sql = "SELECT * FROM (" +
                     "  SELECT a.*, ROWNUM rn FROM (" +
                     "    SELECT * FROM students";
        
        // 添加排序条件
        if (pageBean.getSortField() != null && !pageBean.getSortField().isEmpty()) {
            sql += " ORDER BY " + pageBean.getSortField() + " " + pageBean.getSortOrder();
        } else {
            sql += " ORDER BY student_id ASC";
        }
        
        sql += "  ) a WHERE ROWNUM <= ?" +
               ") WHERE rn > ?";
        
        int end = pageBean.getPageNumber() * pageBean.getPageSize();
        int start = (pageBean.getPageNumber() - 1) * pageBean.getPageSize();
        
        return queryForList(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs) throws SQLException {
                Student student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setBirthDate(rs.getDate("birth_date"));
                student.setAddress(rs.getString("address"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setEnrollmentDate(rs.getDate("enrollment_date"));
                student.setMajor(rs.getString("major"));
                student.setStatus(rs.getString("status"));
                return student;
            }
        }, end, start);
    }
    
    // 获取学生总数
    public int getStudentCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM students";
        return getCount(sql);
    }
    
    // 根据专业查询学生
    public List<Student> getStudentsByMajor(String major) throws SQLException {
        String sql = "SELECT * FROM students WHERE major = ?";
        
        return queryForList(sql, new RowMapper<Student>() {
            @Override
            public Student mapRow(ResultSet rs) throws SQLException {
                Student student = new Student();
                student.setStudentId(rs.getInt("student_id"));
                student.setName(rs.getString("name"));
                student.setGender(rs.getString("gender"));
                student.setBirthDate(rs.getDate("birth_date"));
                student.setAddress(rs.getString("address"));
                student.setPhone(rs.getString("phone"));
                student.setEmail(rs.getString("email"));
                student.setEnrollmentDate(rs.getDate("enrollment_date"));
                student.setMajor(rs.getString("major"));
                student.setStatus(rs.getString("status"));
                return student;
            }
        }, major);
    }
}

